CREATE VIEW [dbo].[vGiftHistorySummary]
AS
SELECT GH.[ID],
    -- first gift
    (SELECT TOP 1 [TransactionDate]
       FROM [vGift] GH1 WITH(NOLOCK)  
     WHERE [OriginalTransaction] =  [dbo].[asi_Get_TransactionID](GH.ID,'FIRST' ) ) as [FirstGiftDate],
    (SELECT TOP 1 [Amount]
       FROM [vGift] GH1 WITH(NOLOCK)
     WHERE [OriginalTransaction] =  [dbo].[asi_Get_TransactionID](GH.ID,'FIRST' ) ) as [FirstGiftAmount],   
    (SELECT TOP 1 [Appeal]
       FROM [vGift] GH1 WITH(NOLOCK)
     WHERE [OriginalTransaction] =  [dbo].[asi_Get_TransactionID](GH.ID,'FIRST' ) ) as [FirstGiftAppeal],
    -- next to last gift    
    (SELECT TOP 1 [TransactionDate]
       FROM [vGift] GH1 WITH(NOLOCK)
     WHERE [OriginalTransaction] =  [dbo].[asi_Get_TransactionID](GH.ID,'NEXTLAST' ) ) as [NextLastGiftDate],
    (SELECT TOP 1 [Amount]
       FROM [vGift] GH1 WITH(NOLOCK)
     WHERE [OriginalTransaction] =  [dbo].[asi_Get_TransactionID](GH.ID,'NEXTLAST' ) ) as [NextLastGiftAmount],   
    (SELECT TOP 1 [Appeal]
       FROM [vGift] GH1 WITH(NOLOCK)  
     WHERE [OriginalTransaction] =  [dbo].[asi_Get_TransactionID](GH.ID,'NEXTLAST' ) ) as [NextLastGiftAppeal],     
     -- Last Gift   
    (SELECT TOP 1 [TransactionDate]
       FROM [vGift] GH1 WITH(NOLOCK)  
     WHERE [OriginalTransaction] =  [dbo].[asi_Get_TransactionID](GH.ID,'LAST' ) ) as [LastGiftDate],
    (SELECT TOP 1 [Amount]
       FROM [vGift] GH1 WITH(NOLOCK)
     WHERE [OriginalTransaction] =  [dbo].[asi_Get_TransactionID](GH.ID,'LAST' ) ) as [LastGiftAmount],   
    (SELECT TOP 1 [Appeal]
       FROM [vGift] GH1 WITH(NOLOCK)
     WHERE [OriginalTransaction] =  [dbo].[asi_Get_TransactionID](GH.ID,'LAST' ) ) as [LastGiftAppeal],
    -- other values    
    [dbo].[asi_GivingStatistics]( GH.ID, 'MIN' ) as [LowestGiftAmount],
    [dbo].[asi_GivingStatistics]( GH.ID, 'MAX' ) as [HighestGiftAmount],    
    CONVERT( int, [dbo].[asi_GivingStatistics]( GH.ID, 'COUNT' )) as [NumberofGifts],    
    [dbo].[asi_GivingStatistics]( GH.ID, 'SUM' ) as [LifetimeGiftValue],    
    [dbo].[asi_GivingStatistics]( GH.ID, 'AVG' ) as [AverageGiftValue],     
    [dbo].[asi_ConsecutiveYearsGiving]( GH.ID ) as [ConsecutiveYearsGiving]
  FROM [dbo].[vGift] GH WITH(NOLOCK)
  WHERE GH.[Amount] <> 0
  GROUP by GH.ID









GO